package com.tuanzi.loan.business.repository.family.impl;

import com.base.common.log.LogService;
import com.google.common.collect.Lists;
import com.tuanzi.loan.business.constants.H5StatCode;
import com.tuanzi.loan.business.entity.family.LoanWholeSaleComment;
import com.tuanzi.loan.business.repository.family.WholeSaleStatRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.family.WholeSaleStatSearchRequest;
import com.tuanzi.loan.vo.response.family.WholeSaleStatSearchResponse;
import com.tuanzi.server.loan_base.util.DateUtils;
import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.impl.TableImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import static com.tuanzi.loan.generated.jooq.loan.tables.LoanSpecifyPush.LOAN_SPECIFY_PUSH;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanWholesaleComment.LOAN_WHOLESALE_COMMENT;

@Repository
public class WholeSaleStatRepositoryImpl extends JOOQBaseRepositoryPlus implements WholeSaleStatRepository{
    @Autowired
    private H5StatCode h5StatCode;

    @Override
    protected TableImpl getTable() {
        return LOAN_SPECIFY_PUSH;
    }

    @Override
    public Page<WholeSaleStatSearchResponse> list(WholeSaleStatSearchRequest searchRequest, Pageable pageable) {
        List<Object> paramList = new ArrayList<Object>();
        String value = " select a.createdate,IFNULL(a.writenum,0) 'writenum' ,IFNULL(b.ordernum ,0) 'ordernum',IFNULL(c.failnum ,0) 'failnum' \n";
        String value1 ="";
        String groupparam = " group by a.createdate";
        String oncondition = "";
        String failcondition = "";
        String tablesql = "( select createdate,mobile,case when c_from='boluodai' or c_from='-1' then null else c_from end c_from,type,status,case when RIGHT(a.city,1)='市' then substr(city,1,CHAR_LENGTH(city)-1) else city end city from loan.loan_specify_push a where house_fund is not null ";
        if (searchRequest.getC_from()!=null&&searchRequest.getType()!=null){
            if (!"全部".equals(searchRequest.getC_from())){
                value = value+",a.c_from";
                value1 = value1+",c_from";
                groupparam = groupparam+" ,a.c_from";
                oncondition = oncondition+" and (a.c_from = b.c_from or a.c_from is null and b.c_from is null)";
                failcondition = failcondition+" and (a.c_from = c.c_from or a.c_from is null and c.c_from is null)";
            }
            if (!"全部".equals(searchRequest.getType())){
                value = value+",a.type";
                value1 = value1 +",type";
                groupparam = groupparam+",a.type";
                oncondition = oncondition+" and a.type = b.type";
                failcondition = failcondition+" and a.type = c.type";
            }
        }
        if (searchRequest.getStartdate()!=null){
            tablesql = tablesql+" and createdate>='"+DateUtils.formatDateToYyyyMMdd(searchRequest.getStartdate())+"'";
        }
        if (searchRequest.getEnddate()!=null){
            tablesql = tablesql+" and createdate<='"+DateUtils.formatDateToYyyyMMdd(searchRequest.getEnddate())+"'";
        }
        StringBuilder sql = new StringBuilder();
        sql.append(value);
        if (searchRequest.getType().equals("全部")){
            sql.append(" from (select a.createdate,count(distinct a.mobile) 'writenum'"+value1+" from  \n");
        }else{
            sql.append(" from (select distinct a.createdate,count(a.mobile) 'writenum'"+value1+" from  \n");
        }
        sql.append(tablesql+") a\n");
        sql.append("where 1=1  \n");
        sql.append(groupparam+" ) a\n");
        sql.append("left join\n");
        sql.append("(select a.createdate,count( mobile) 'ordernum'"+value1+" from \n");
        sql.append(tablesql+" ) a\n");
        sql.append("where `status`=1  \n");
        sql.append(groupparam+") b on a.createdate = b.createdate "+oncondition+"\n");
        //获取status=2
        sql.append("left join \n");
        sql.append("(select a.createdate,count(mobile) 'failnum'"+value1+" from \n");
        sql.append(tablesql+" ) a\n");
        sql.append("where `status`=2  \n");
        sql.append(groupparam+") c on a.createdate = c.createdate "+failcondition+"\n");

        sql.append("where 1=1 ");
        if (searchRequest.getType()!=null&&!"请选择".equals(searchRequest.getType())&&!"全部".equals(searchRequest.getType())){
            sql.append(" and a.type = ?");
            paramList.add(searchRequest.getType());
        }
        if (searchRequest.getC_from()!=null&&!"请选择".equals(searchRequest.getC_from())&&!"全部".equals(searchRequest.getC_from())){
            if ("".equals(searchRequest.getC_from())){
                sql.append(" and a.c_from is null");
            }else{
                sql.append(" and a.c_from = '"+searchRequest.getC_from()+"'");
            }

        }
        Long count = dslContext.fetchOne("SELECT COUNT(*) AS num FROM (" + sql.toString() + ") AS c", paramList.toArray()).into(Long.class);
        paramList.add(pageable.getOffset());
        paramList.add(pageable.getPageSize());
        LogService.info("sql:"+sql);
        return new PageImpl<WholeSaleStatSearchResponse>(dslContext.fetch(sql.toString()+" LIMIT ?,?",paramList.toArray()).map(new RecordMapper<Record, WholeSaleStatSearchResponse>(){

            @Override
            public WholeSaleStatSearchResponse map(Record record) {
                WholeSaleStatSearchResponse wholeSaleStatSearchResponse = new WholeSaleStatSearchResponse();
                wholeSaleStatSearchResponse.setDate(record.get("createdate", Date.class));
                wholeSaleStatSearchResponse.setOrderuser(record.get("ordernum",Integer.class));
                if (searchRequest.getC_from()!=null&&!"全部".equals(searchRequest.getC_from())){
                    wholeSaleStatSearchResponse.setFrom_id(translate(record.get("c_from",String.class)));
                }else{
                    wholeSaleStatSearchResponse.setFrom_id("全部");
                }
                if (searchRequest.getType()!=null&&!"全部".equals(searchRequest.getType())){
                    wholeSaleStatSearchResponse.setOwner(getOwnnermap().get(record.get("type",Integer.class)).toString());
                }else{
                    wholeSaleStatSearchResponse.setOwner("全部");
                }
                wholeSaleStatSearchResponse.setWritenum(record.get("writenum",Integer.class));
                wholeSaleStatSearchResponse.setFaileorder(record.get("failnum",Integer.class));
                wholeSaleStatSearchResponse.setWriteorder(wholeSaleStatSearchResponse.getWriteorder());
                wholeSaleStatSearchResponse.setEarnordernum(wholeSaleStatSearchResponse.getEarnordernum());
                wholeSaleStatSearchResponse.setWriteallorder(wholeSaleStatSearchResponse.getWriteallorder());
                return wholeSaleStatSearchResponse;
            }

            public String translate(Object from_id){
                if ("chezhu".equals(from_id)){
                    return "车主";
                }else if ("".equals(from_id)||from_id==null||"null".equals(from_id)){
                    return "菠萝贷站内";
                }else{
                    return from_id.toString();
                }
            }
        }), pageable, count);


    }

    @Override
    public List<String> findDistinctCfrom() {

        List<String> resultList =
                dslContext.selectDistinct(LOAN_SPECIFY_PUSH.C_FROM).from(LOAN_SPECIFY_PUSH)
                        .where(LOAN_SPECIFY_PUSH.C_FROM.isNotNull())
                        .and(LOAN_SPECIFY_PUSH.C_FROM.notEqual("chezhu"))
                        .and(LOAN_SPECIFY_PUSH.C_FROM.notEqual("boluodai"))
                        .and(LOAN_SPECIFY_PUSH.C_FROM.notEqual("-1"))
                .fetch(LOAN_SPECIFY_PUSH.C_FROM);
        return resultList;
    }

    public List<LoanWholeSaleComment> findLoanWholeSaleComment(){
        List<LoanWholeSaleComment> result = Lists.newArrayList();
         result =
                dslContext.select(LOAN_WHOLESALE_COMMENT.fields()).from(LOAN_WHOLESALE_COMMENT)
                .orderBy(LOAN_WHOLESALE_COMMENT.TYPE).fetchInto(LoanWholeSaleComment.class);
        return result;
    }

    public Map<Integer,Object> getOwnnermap() {
        Map<Integer,Object> ownnermap = new ConcurrentHashMap<>();
        findLoanWholeSaleComment().forEach((k)->{
            ownnermap.put(k.getType(),k.getOwnername());
        });
        return ownnermap;
    }
}
